﻿if object_id('fn_MapHTML_AssetXmissionStructure3') is not null
	DROP FUNCTION fn_MapHTML_AssetXmissionStructure3

GO

CREATE FUNCTION [dbo].[fn_MapHTML_AssetXmissionStructure3]
(
	@poleId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @urlInspectionVisit varchar(max)
	declare @urlGPSVisit varchar(max)

	select 
		@urlInspectionVisit = SettingValue
	from 
		tb_Settings 
	where 
		SettingId = 23

	select 
		@urlGPSVisit = SettingValue
	from 
		tb_Settings 
	where 
		SettingId = 23

	SELECT @urlInspectionVisit = @urlInspectionVisit + 'InspVisitXmissionStructure.aspx'
	SELECT @urlGPSVisit = @urlGPSVisit + 'GpsVisitXmissionStructure.aspx'

	declare @img xml
	set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html varchar(max)
	set @html = 
	(

select
	 'Xmission Pole' as 'TITLE'
	,'Inspection History' as 'HistoryTitle'
	,Poles.Utility_Asset_XMissionStructures_PoleID as 'PoleId'
	,Utility_Asset_XMissionStructureFK as 'StructureId'
	,STR(Poles.Latitude, 9, 9) as 'Latitude'
	,STR(Poles.Longitude, 9, 9)  as 'Longitude'
	,Poles.Number
	,Poles.SectionFk as 'SectionName'
	,maps.Name as 'MapName'
	,tops.Name as 'TopName'
	,species.Name as 'SpecieName'
	,classes.Name as 'ClassName'
	,treatments.Name as 'TreatmentName'
	,cast(Poles.length as int) 'LenghtName'
	,(select 
		'Inspection Visit' as 'TITLE'
		,year(SubVisit.AcquisitionDate) AS 'YearVisited'
		,vendors.Name as 'VendorName'
		,SubVisit.Vendor_InspVisit_XMissionStructure_PoleId AS 'ID'
		,SubVisit.LInspector AS 'LastVisitorName'
		,YEAR(SubVisit.LVisit) AS 'LastVisitorDate'
		,@urlInspectionVisit + '?ID=' + CONVERT(varchar, SubVisit.Vendor_InspVisit_XMissionStructure_PoleId) AS urlInspectionVisit
		-- visit images <IMAGE href="" src="" />
		,(select
			 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_InspVisit_XMissionStructures_ImageId), '{1}', 'InspectionPoleXmission'), '{2}', 85), '{3}', 85) '@src'
			--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480) as '@href'
			,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_InspVisit_XMissionStructures_ImageId), '{1}', 'InspectionPoleXmission'), '{2}', 640), '{3}', 480)) as '@href'
			,Images.*
		from 
			tb_Vendor_InspVisit_XmissionStructures_Images as Images
		where 
			Images.Vendor_InspVisit_XMissionStructure_PoleFK = SubVisit.Vendor_InspVisit_XMissionStructure_PoleId
		for xml path ('IMAGE'), type) as 'IMAGES'
	from 
		tb_Vendor_InspVisit_XmissionStructures_Poles as SubVisit
	left join tb_vendor_projects vendorProjects
		on vendorProjects.Vendor_ProjectId = SubVisit.Vendor_ProjectFk
	left join tb_vendors vendors
		on vendors.VendorId = vendorProjects.VendorFk
	where
		SubVisit.Utility_Asset_XMissionStructures_PoleFk = Poles.Utility_Asset_XMissionStructures_PoleID
	order by
		SubVisit.AcquisitionDate DESC
	for xml path ('VISIT'), type) as 'VISITS'

	,(select 
		'GPS Visit' as 'TITLE'
		,Year(SubVisit.SatelliteTime) AS 'YearVisited'
		,vendors.Name as 'VendorName'
		,SubVisit.Vendor_Visit_XMissionStructures_PoleId AS 'ID'
		,ISNULL(LVisitor, '') AS 'LastVisitorName'
		,LVisit AS 'LastVisitorDate'
		,@urlGPSVisit + '?ID=' + CONVERT(varchar, SubVisit.Vendor_Visit_XMissionStructures_PoleId) AS urlInspectionVisit
		-- visit images <IMAGE href="" src="" />
		,(select
			 replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId), '{1}', 'GPSPoleXmission'), '{2}', 85), '{3}', 85) '@src'
			--,replace(replace(replace(replace(Href.SettingValue, '{0}', Images.tb_Vendor_Distribution_Images_ImageId), '{1}', 'InspectionPoleDistribution'), '{2}', 640), '{3}', 480) as '@href'
			,replace(Href.SettingValue, '{0}', replace(replace(replace(replace(Src.SettingValue, '{0}', Images.Vendor_Visit_XMissionStructures_ImageId), '{1}', 'GPSPoleXmission'), '{2}', 640), '{3}', 480)) as '@href'
			,Images.*
		from 
			tb_Vendor_Visit_XMissionStructures_Images as Images
		where 
			Images.Vendor_Visit_XMissionStructures_PoleFK = SubVisit.Vendor_Visit_XMissionStructures_PoleId
		for xml path ('IMAGE'), type) as 'IMAGES'
	from 
		tb_Vendor_Visit_XMissionStructures_Poles as SubVisit
	inner join tb_Vendor_Visit_XMissionStructures st
		on st.Vendor_Visit_XMissionStructureId =  SubVisit.Vendor_Visit_XMissionStructureFK
	left join tb_vendor_projects vendorProjects
		on vendorProjects.Vendor_ProjectId = st.Vendor_ProjectFk
	left join tb_vendors vendors
		on vendors.VendorId = vendorProjects.VendorFk
	where
		SubVisit.Utility_Asset_XMissionStructures_PoleFk = Poles.Utility_Asset_XMissionStructures_PoleID
	order by
		SubVisit.SatelliteTime DESC
	for xml path ('VISIT'), type) as 'VISITS'


from
	tb_Utility_Asset_XMissionStructures as Structures
	join tb_Utility_Asset_XMissionStructures_Poles poles
		on Structures.Utility_Asset_XMissionStructureId = poles.Utility_Asset_XMissionStructureFK
	left join tb_Distributions_TopTypes tops
		on tops.Distributions_TopTypeId = poles.XMissionStructures_PoleTopTypeFk
	left join tb_Utility_Polygons maps 
		on maps.Utility_PolygonId = poles.MapFk
	left join tb_XMissionStructures_PoleSpecies species 
		on poles.XMissionStructures_PoleSpeciesFk = species.XMissionStructures_PoleSpeciesId
	left join tb_XMissionStructures_PoleClasses classes 
		on poles.XMissionStructures_PoleClassFk  = classes.XMissionStructures_PoleClassId
	left join tb_Distribution_Lengths lenghts 
		on poles.Length = lenghts.LengthId
	left join tb_XMissionStructures_PoleTreatments treatments
		on treatments.XMissionStructures_PoleTreatmentId = poles.XMissionStructures_PoleTreatmentFk
	join tb_Settings as Href on Href.SettingId = 25
	join tb_Settings as Src on Src.SettingId = 24
where
	Poles.Utility_Asset_XMissionStructures_PoleID = @poleId
for xml path ('Pole')
	
	)
	return @html

END
